package com.zfzs.post.exl;

import org.apache.poi.ss.usermodel.*;
import org.springframework.web.multipart.MultipartFile;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * @description: exl导入
 * @author: ck
 * @create: 2019-09-04 10:06
 **/
public class ExlUtils {

    public static Workbook getWorkbook(MultipartFile attach) {
        Workbook wb = null;
        try {
            InputStream in = attach.getInputStream();
            wb = WorkbookFactory.create(in);
        } catch (IOException e) {
            System.out.println("找不到文件");
        } catch (InvalidFormatException e) {
            System.out.println("类型错误");
        }
        return wb;
    }

    /**
     * 取Excel所有数据，包含header
     *
     * @return List<String[]>
     */
    public static List<String[]> getAllData(int sheetIndex, Workbook wb) {
        List<String[]> dataList = new ArrayList<String[]>(100);
        int columnNum = 0;
        Sheet sheet = wb.getSheetAt(sheetIndex);
        if (sheet.getRow(0) != null) {
            columnNum = sheet.getRow(0).getLastCellNum() - sheet.getRow(0).getFirstCellNum();
        }
        if (columnNum > 0) {
            for (Row row : sheet) {
                String[] singleRow = new String[columnNum];
                int n = 0;
                for (int i = 0; i < columnNum; i++) {
                    Cell cell = row.getCell(i, Row.CREATE_NULL_AS_BLANK);
                    switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_BLANK:
                            singleRow[n] = "";
                            break;
                        case Cell.CELL_TYPE_BOOLEAN:
                            singleRow[n] = Boolean.toString(cell.getBooleanCellValue());
                            break;
                        //数值
                        case Cell.CELL_TYPE_NUMERIC:
                            if (DateUtil.isCellDateFormatted(cell)) {
                                singleRow[n] = String.valueOf(cell.getDateCellValue());
                            } else {
                                cell.setCellType(Cell.CELL_TYPE_STRING);
                                String temp = cell.getStringCellValue();
                                //判断是否包含小数点，如果不含小数点，则以字符串读取，如果含小数点，则转换为Double类型的字符串
                                if (temp.indexOf(".") > -1) {
                                    singleRow[n] = String.valueOf(new Double(temp)).trim();
                                } else {
                                    singleRow[n] = temp.trim();
                                }
                            }
                            break;
                        case Cell.CELL_TYPE_STRING:
                            singleRow[n] = cell.getStringCellValue().trim();
                            break;
                        case Cell.CELL_TYPE_ERROR:
                            singleRow[n] = "";
                            break;
                        case Cell.CELL_TYPE_FORMULA:
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            singleRow[n] = cell.getStringCellValue();
                            if (singleRow[n] != null) {
                                singleRow[n] = singleRow[n].replaceAll("#N/A", "").trim();
                            }
                            break;
                        default:
                            singleRow[n] = "";
                            break;
                    }
                    n++;
                }
                if ("".equals(singleRow[0])) {
                    continue;
                }//如果第一行为空，跳过
                dataList.add(singleRow);
            }
        }
        return dataList;
    }
}
